with tab as (
 select billcode,
       scantype,
       scansitecode,
       split(concat(scantime,'\001',
       coalesce(inputtime,''),'\001',
       coalesce(send_deliver_user,''),'\001',
       coalesce(send_deliver_usercode,''),'\001',
       coalesce(expresstype,''),'\001',
       coalesce(nextstationcode,''),'\001',
       coalesce(operatedate,''),'\001',
       coalesce(packagecode,''),'\001',
       coalesce(scanuser,''),'\001',
       coalesce(scanusercode,''),'\001',
       coalesce(source,''),'\001',
       coalesce(subbillcode,''),'\001',
       coalesce(weight,''),'\001',
       coalesce(scansitecode,'')
       ),'\001') as wid_columns
from jms_ods.tab_barscan_collect      --21天内有效
where  dt BETWEEN date_add('{{ execution_date | cst_ds }}', -24) AND '{{ execution_date | cst_ds }}'
)

insert overwrite table jms_dwd.dwd_barscan_collect_dt
select
billcode                                                 as billcode,
max(wid_columns)[13]                                     as scansitecode_later,
max(wid_columns)[0]                                      as scantime_later,
max(wid_columns)[1]                                      as inputtime_later,
max(wid_columns)[2]                                      as send_deliver_user_later,
max(wid_columns)[3]                                      as send_deliver_usercode_later,
max(wid_columns)[4]                                      as expresstype_later,
max(wid_columns)[5]                                      as nextstationcode_later,
max(wid_columns)[6]                                      as operatedate_later,
max(wid_columns)[7]                                      as packagecode_later,
max(wid_columns)[8]                                      as scanuser_later,
max(wid_columns)[9]                                      as scanusercode_later,
max(wid_columns)[10]                                     as source_later,
max(wid_columns)[11]                                     as subbillcode_later,
max(wid_columns)[12]                                     as weight_later,
scantype                                                 as scantype_later,

min(wid_columns)[13]                                     as scansitecode_early,
min(wid_columns)[0]                                      as scantime_early,
min(wid_columns)[1]                                      as inputtime_early,
min(wid_columns)[2]                                      as send_deliver_user_early,
min(wid_columns)[3]                                      as send_deliver_usercode_early,
min(wid_columns)[4]                                      as expresstype_early,
min(wid_columns)[5]                                      as nextstationcode_early,
min(wid_columns)[6]                                      as operatedate_early,
min(wid_columns)[7]                                      as packagecode_early,
min(wid_columns)[8]                                      as scanuser_early,
min(wid_columns)[9]                                      as scanusercode_early,
min(wid_columns)[10]                                     as source_early,
min(wid_columns)[11]                                     as subbillcode_early,
min(wid_columns)[12]                                     as weight_early,
scantype                                                 as scantype_early,
'billcode'                                               as dimtype,
date(max(wid_columns)[0])                                as dt

from tab
 group by billcode,scantype
having
    date(max(wid_columns)[0]) between date_add('{{ execution_date | cst_ds }}', -14) AND '{{ execution_date | cst_ds }}'

union all

select
billcode                                                 as billcode,
scansitecode                                             as scansitecode_later,
max(wid_columns)[0]                                      as scantime_later,
max(wid_columns)[1]                                      as inputtime_later,
max(wid_columns)[2]                                      as send_deliver_user_later,
max(wid_columns)[3]                                      as send_deliver_usercode_later,
max(wid_columns)[4]                                      as expresstype_later,
max(wid_columns)[5]                                      as nextstationcode_later,
max(wid_columns)[6]                                      as operatedate_later,
max(wid_columns)[7]                                      as packagecode_later,
max(wid_columns)[8]                                      as scanuser_later,
max(wid_columns)[9]                                      as scanusercode_later,
max(wid_columns)[10]                                     as source_later,
max(wid_columns)[11]                                     as subbillcode_later,
max(wid_columns)[12]                                     as weight_later,
scantype                                                 as scantype_later,

scansitecode                                             as scansitecode_early,
min(wid_columns)[0]                                      as scantime_early,
min(wid_columns)[1]                                      as inputtime_early,
min(wid_columns)[2]                                      as send_deliver_user_early,
min(wid_columns)[3]                                      as send_deliver_usercode_early,
min(wid_columns)[4]                                      as expresstype_early,
min(wid_columns)[5]                                      as nextstationcode_early,
min(wid_columns)[6]                                      as operatedate_early,
min(wid_columns)[7]                                      as packagecode_early,
min(wid_columns)[8]                                      as scanuser_early,
min(wid_columns)[9]                                      as scanusercode_early,
min(wid_columns)[10]                                     as source_early,
min(wid_columns)[11]                                     as subbillcode_early,
min(wid_columns)[12]                                     as weight_early,
scantype                                                 as scantype_early,
'bill_scansite'                                          as dimtype,
date(max(wid_columns)[0])                                as dt
from tab
 group by billcode,scantype,scansitecode
having
    date(max(wid_columns)[0]) between date_add('{{ execution_date | cst_ds }}', -14) AND '{{ execution_date | cst_ds }}'
    distribute by dt ,dimtype, pmod(hash(rand()),8);
